import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class homework {
    //数据库连接四要素
    //1.驱动
    String driver = "com.mysql.cj.jdbc.Driver";
    //2.数据库地址
    String url = "jdbc:mysql://127.0.0.1:3306/briup";
    //3.用户名
    String username = "briup";
    //4.密码
    String password = "lzl20041016";

    @Test
    public void getConnection() {
        Connection conn = null;
        try {
            //1.注册驱动
            Class.forName(driver);
            //2.获取连接对象
            conn = DriverManager.getConnection(url, username, password);
            System.out.println(conn);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6.释放资源
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    //创建表
    @Test
    public void createTable() {
        //数据库连接对象
        Connection conn = null;
        //数据库操作对象
        Statement stmt = null;
        try {
            Class.forName(driver);
            //获取连接对象
            conn = DriverManager.getConnection(url, username, password);
            //3.获取操作对象
            stmt = conn.createStatement();
            //4.执行sal语句
            String sql = "create table t_customer("
                    + "id INTEGER PRIMARY KEY,"
                    + "name VARCHAR(50) NOT NULL,"
                    + "age INTEGER ,"
                    +"birthday date,"
                    +"register_time date"
                    + ")";
            int rows = stmt.executeUpdate(sql);
            //5.处理结果
            System.out.println("返回结果rows:" + rows);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6.释放资源
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception err) {
                    err.printStackTrace();
                }
            }
        }
    }

    //插入数据
    @Test
    public void insertData() {
        Connection conn = null;
        Statement stmt = null;
        try {
            //1.加载驱动
            Class.forName(driver);
            //2.获取连接对象
            conn = DriverManager.getConnection(url, username, password);
            //3.获取操作对象
            stmt = conn.createStatement();
            //4.执行sql语句
            String sql1 =("INSERT INTO t_customer(id,name,age,birthday,register_time) VALUES(1,'张三',21,'2002-04-11','2024-06-23')");
            String sql2 =("INSERT INTO t_customer(id,name,age,birthday,register_time) VALUES(2,'李四',23,'2001-01-01','2024-06-23')");
            String sql3 =("INSERT INTO t_customer(id,name,age,birthday,register_time) VALUES(3,'王五',24,'2002-01-01','2024-06-23')");
            String sql4 =("INSERT INTO t_customer(id,name,age,birthday,register_time) VALUES(4,'赵六',25,'1999-04-11','2024-06-23')");
            // 执行多个sql语句
            stmt.addBatch(sql1);
            stmt.addBatch(sql2);
            stmt.addBatch(sql3);
            stmt.addBatch(sql4);

            // 开始处理
            int[] rows = stmt.executeBatch();
            //5.处理结果
            System.out.println("修改的行数rows:" + rows);
        } catch (Exception e) {
            //6.释放资源
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (Exception err) {
                    err.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e1) {
                    e1.printStackTrace();
                }
            }
        }
    }
    //查询
    @Test
    public void getData(){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<Customer> list =new ArrayList<Customer>();// 创建一个List来存储Customer对象
        try{
            //1.加载驱动
            Class.forName(driver);
            //2.获取连接对象
            conn = DriverManager.getConnection(url,username,password);
            //3.获取操作对象
            stmt = conn.createStatement();
            //4.执行sql语句
            String sql = "select * from t_customer where birthday < '2000-01-01' ";
            rs = stmt.executeQuery(sql);//注意这里执行的是查询语句
            //5.处理结果
            while(rs.next()){
                int id = rs.getInt("id");
                String name =  rs.getString("name");
                int age =  rs.getInt("age");
                String birthday =  rs.getString("birthday");
                String register_time =  rs.getString("register_time");
                //System.out.println("ID: " + id + ",Name: " + name + ",Age: " + age+"，birthday:"+birthday+"，register_time:"+register_time);
                Customer c = new Customer(id,name,age,birthday,register_time);
                System.out.println(c);
                list.add(c);

            }
            //System.out.println(list);
            System.out.println(list.size());
        }catch(Exception e){
            //6.释放资源
            if(rs!=null){
                try{
                    rs.close();
                }catch (Exception e1){
                    e1.printStackTrace();
                }
            }
            if(stmt!=null){
                try{
                    stmt.close();
                }catch (Exception err){
                    err.printStackTrace();
                }
            }
            if(conn!=null){
                try{
                    conn.close();
                }catch (Exception e1){
                    e1.printStackTrace();
                }
            }
        }
    }

}